package SSP;
import java.sql.*;

public class ServiceOrder {

	private CallableStatement csmt = null;
	private ResultSet rs = null;
	private PreparedStatement ps = null;
	private OraDB myDB = new OraDB();
	private int myequipmentid = 0;
	private int productid = 0;
	private String productname = null;
	
	private int equipmentid = 0;
	private int servicetypeid = 0;
	private int priorityid = 0;
	private int pmid = 0;
	private int regionid = 0;
	private String problemdescription;
	private String contactname = null;
	private String contactphone = null;
	private String contactemail = null;
	

	public void setEquipmentId(int equipmentid)
	throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException {
		myequipmentid = equipmentid;
		myDB.createConnection();
		String myQuery = "select productid from equipment where equipmentid = ?";
		ps = myDB.conn.prepareStatement(myQuery);
		ps.setInt(1,myequipmentid); 
		rs = ps.executeQuery();
		while (rs.next()) {
			productid = rs.getInt("productid");
		}
		rs.close();
		myDB.closeConnection();	
	}
	
	public void setCustomerid(int customerid)
	throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException {
		myDB.createConnection();
		String myQuery = "select regionid " +
				"from state s, users u " +
				"where u.statecode=s.statecode and u.customerid=?";
		ps = myDB.conn.prepareStatement(myQuery);
		ps.setInt(1,customerid); 
		rs = ps.executeQuery();
		while (rs.next()) {
			regionid = rs.getInt("regionid");
		}
		rs.close();
		myDB.closeConnection();	
	}
	
	public ResultSet getNewServiceOrder()
	throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException {
		myequipmentid = equipmentid;
		myDB.createConnection();
		String myQuery = "select so.serviceorderid, c.CustomerName, sos.soStatusDescription, " +
				"pri.PriorityDescription, st.ServiceTypeDescription, " +
				"to_char(so.opendate, 'MM/DD/YYYY') as opendate, l.address1, l.address2, " +
				"l.city || ', ' || l.state || '  '|| l.zip as CityStateZip, " +
				"so.contactname, so.contactphone, so.contactemail, " +
				"so.equipmentid, p.productname, m.ModalityDescription, " +
				"so.problemdescription " +
				"from serviceorder so, customer c, serviceorderstatus sos, " +
				"priority pri, servicetype st, location l, product p, modality m, equipment e " +
				"where st.servicetypeid=so.servicetypeid and " +
				"pri.priorityid=so.priorityid and " +
				"sos.SoStatusID = so.SoStatusID and " +
				"so.equipmentid = e.equipmentid and " +
				"e.locationid = l.locationid and " +
				"l.customerid = c.customerid and " +
				"e.productid = p.productid and " +
				"p.modalityid = m.modalityid and " +
				"so.serviceorderid = (select max(serviceorderid) from serviceorder)";
		ps = myDB.conn.prepareStatement(myQuery);
		rs = ps.executeQuery();
		return rs;
	}
	
	public ResultSet getServiceOrderDetails(int serviceorderid)
	throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException {
		myequipmentid = equipmentid;
		myDB.createConnection();
		String myQuery = "select so.serviceorderid, c.CustomerName, sos.soStatusDescription, " +
		"pri.PriorityDescription, st.ServiceTypeDescription, " +
		"to_char(so.opendate, 'MM/DD/YYYY') as opendate, l.address1, l.address2, " +
		"l.city || ', ' || l.state || '  '|| l.zip as CityStateZip, " +
		"so.equipmentid, p.productname, m.ModalityDescription, " +
		"c.contactfirstname || ' ' || c.contactlastname as contactname, " +
		"c.contactphone, c.contactemail, " +
		"so.problemdescription, so.TimeEstimate " +
		"from serviceorder so, customer c, serviceorderstatus sos, priority pri, " +
		"servicetype st, location l, product p, modality m, equipment e " +
		"where st.servicetypeid=so.servicetypeid and " +
		"pri.priorityid=so.priorityid and " +
		"sos.SoStatusID = so.SoStatusID and " +
		"so.equipmentid = e.equipmentid and " +
		"e.locationid = l.locationid " +
		"and l.customerid = c.customerid and " +
		"e.productid = p.productid and " +
		"p.modalityid = m.modalityid and " +
		"so.serviceorderid = ?";
		ps = myDB.conn.prepareStatement(myQuery);
		ps.setInt(1,serviceorderid);
		rs = ps.executeQuery();
		return rs;
	}
	
	public ResultSet getActionDetails(int serviceorderid)
	throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException {
		myequipmentid = equipmentid;
		myDB.createConnection();
		String myQuery = "select rownum as actionnumber, " +
				"to_char(checkin,  'MM/DD/YYYY  HH:MI  AM') as checkin, " + 
		  		"to_char(checkout, 'MM/DD/YYYY  HH:MI  AM') as checkout, " +
		  		"u.firstname || ' ' || u.lastname as name, " + 
		  		"actionnotes " +
		  		"from serviceorderaction soa, users u " +
		  		"where u.userid = soa.userid and soa.serviceorderid = ? " +
		  		"order by 2";
		ps = myDB.conn.prepareStatement(myQuery);
		ps.setInt(1,serviceorderid);
		rs = ps.executeQuery();
		return rs;
	}
	
	public ResultSet getServiceOrderDetails2(int serviceorderid)
	throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException {
		myequipmentid = equipmentid;
		myDB.createConnection();
		String myQuery = "select so.serviceorderid, c.CustomerName, sos.soStatusDescription, " +
				"pri.PriorityDescription, st.ServiceTypeDescription, " +
				"to_char(so.opendate, 'MM/DD/YYYY') as opendate, l.address1, l.address2, " +
				"l.city || ', ' || l.state || '  '|| l.zip as CityStateZip, " +
				"so.contactname, so.contactphone, so.contactemail, " +
				"so.equipmentid, p.productname, m.ModalityDescription, " +
				"so.problemdescription, so.TimeEstimate, soa.actionnotes " +
				"from serviceorder so,serviceorderaction soa, customer c, " +
				"serviceorderstatus sos, priority pri, servicetype st, " +
				"location l, product p, modality m, equipment e " +
				"where st.servicetypeid=so.servicetypeid and " +
				"pri.priorityid=so.priorityid and " +
				"sos.SoStatusID = so.SoStatusID and " +
				"so.equipmentid = e.equipmentid and " +
				"e.locationid = l.locationid and " +
				"l.customerid = c.customerid and " +
				"e.productid = p.productid and " +
				"p.modalityid = m.modalityid and " +
				"so.serviceorderid = ? and " +
				"so.serviceorderid = soa.serviceorderid and " +
				"soa.soActionID = (select max(soActionID) " +
				                   "from serviceorderaction where serviceorderid = ?)";
		ps = myDB.conn.prepareStatement(myQuery);
		ps.setInt(1,serviceorderid);
		ps.setInt(2,serviceorderid);
		rs = ps.executeQuery();
		return rs;
	}
	
	public String getProduct() 
	throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException {
		myDB.createConnection();
		String myQuery = "select productname " +
					     "from product " +
					     "where productid = ?";
		ps = myDB.conn.prepareStatement(myQuery);
		ps.setInt(1,productid);
		rs = ps.executeQuery();
		while (rs.next()) {
			productname = rs.getString("productname");
		}
		rs.close();
		myDB.closeConnection();
		return productname;
	}
	
	public String CheckSoAction(int serviceorderid) 
	throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException {
		myDB.createConnection();
		String myserviceorderid = null;
		String soaction = null;
		String myQuery = "select max(soActionID)as soActionID " +
				         "from ServiceOrderAction " +
				         "where ServiceOrderID = ?";
		ps = myDB.conn.prepareStatement(myQuery);
		ps.setInt(1,serviceorderid);
		rs = ps.executeQuery();
		while (rs.next()) {
			myserviceorderid = rs.getString("soActionID");
		}
		rs.close();
		myDB.closeConnection();

		if (myserviceorderid == null) {
			
			soaction = "false";
		}
		else {
			soaction = "true";
		}
		
		return soaction;
	}
	
	
	public ResultSet getCheckinCheckout(int serviceorderid) 
	throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException {
		myDB.createConnection();
		String myQuery = "select CheckIn, CheckOut " +
				         "from ServiceOrderAction " +
				         "where ServiceOrderID = ? and " +
				         "soActionID = (select max(soActionID) from ServiceOrderAction where ServiceOrderID = ?)";
		ps = myDB.conn.prepareStatement(myQuery);
		ps.setInt(1,serviceorderid);
		ps.setInt(2,serviceorderid);
		rs = ps.executeQuery();
		return rs;
	}

	public ResultSet getServiceTypes() 
	throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException {
		myDB.createConnection();
		String myQuery = "select servicetypeid, servicetypedescription " +
				         "from servicetype";
		ps = myDB.conn.prepareStatement(myQuery);
		rs = ps.executeQuery();
		return rs;
	}
	
	public ResultSet getPriority() 
	throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException {
		myDB.createConnection();
		String myQuery = "select priorityid, prioritydescription " +
				         "from priority";
		ps = myDB.conn.prepareStatement(myQuery);
		rs = ps.executeQuery();
		return rs;
	}
	
	public ResultSet getPMs() 
	throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException {
		myDB.createConnection();
		String myQuery = "select pmid, pmDescription " +
				         "from PreventiveMaintenance";
		ps = myDB.conn.prepareStatement(myQuery);
		rs = ps.executeQuery();
		return rs;
	}
	
	public ResultSet getStatusIn() 
	throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException {
		myDB.createConnection();
		String myQuery = "select SoStatusID, soStatusDescription " +
				         "from ServiceOrderStatus " +
				         "where SOStatusID not in (1,6,7,10) order by 2";
		ps = myDB.conn.prepareStatement(myQuery);
		rs = ps.executeQuery();
		return rs;
	}
	
	public ResultSet getStatusOut() 
	throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException {
		myDB.createConnection();
		String myQuery = "select SoStatusID, soStatusDescription " +
				         "from ServiceOrderStatus " +
				         "where SOStatusID not in (6,7,10) order by 2";
		ps = myDB.conn.prepareStatement(myQuery);
		rs = ps.executeQuery();
		return rs;
	}
	
	public ResultSet getStatusSpecific(int SoStatusID) 
	throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException {
		myDB.createConnection();
		String myQuery = "select soStatusDescription " +
				         "from ServiceOrderStatus " +
				         "where SoStatusID = ?";
		ps = myDB.conn.prepareStatement(myQuery);
		ps.setInt(1,SoStatusID);
		rs = ps.executeQuery();
		return rs;
	}
	
	 public int getEquipmentid() {
		return equipmentid;
	}

	public void setEquipmentid(int equipmentid) {
		this.equipmentid = equipmentid;
	}

	public int getServicetypeid() {
		return servicetypeid;
	}

	public void setServicetypeid(int servicetypeid) {
		this.servicetypeid = servicetypeid;
	}

	public int getPriorityid() {
		return priorityid;
	}

	public void setPriorityid(int priorityid) {
		this.priorityid = priorityid;
	}

	public int getPmid() {
		return pmid;
	}

	public void setPmid(int pmid) {
		this.pmid = pmid;
	}

	public String getProblemdescription() {
        if(problemdescription==null) problemdescription="";		
		return problemdescription;
	}

	public void setProblemdescription(String problemdescription) {
		this.problemdescription = problemdescription;
	}

	public String getContactname() {
        if(contactname==null) contactname="";			
		return contactname;
	}

	public void setContactname(String contactname) {
		this.contactname = contactname;
	}

	public String getContactphone() {
        if(contactphone==null) contactphone="";		
		return contactphone;
	}

	public void setContactphone(String contactphone) {
		this.contactphone = contactphone;
	}

	public String getContactemail() {
        if(contactemail==null) contactemail="";			
		return contactemail;
	}

	public void setContactemail(String contactemail) {
		this.contactemail = contactemail;
	}

	public void ServiceOrderCreate()
	 throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException
	{
	 String mySQL = "{call ServiceOrderCreate(?,?,?,?,?,?,?,?,?)}";
	 myDB.createConnection();
	 csmt = myDB.conn.prepareCall(mySQL);
	 csmt.setInt(1, equipmentid);
	 csmt.setInt(2, servicetypeid);
	 csmt.setInt(3, priorityid);
	 if (pmid == 0) {
		 csmt.setString(4, null);
	 }
	 else {
		 csmt.setInt(4, pmid); 
	 }
	 csmt.setInt(5, regionid);
	 csmt.setString(6, problemdescription);
	 csmt.setString(7, contactname);
	 csmt.setString(8, contactphone);
	 csmt.setString(9, contactemail);
	 csmt.executeQuery();
	 myDB.closeConnection();
	}
	
	public void ServiceOrderAccept(int serviceorderid)
	 throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException
	{
	 String mySQL = "{call ServiceOrderAccept(?)}";
	 myDB.createConnection();
	 csmt = myDB.conn.prepareCall(mySQL);
	 csmt.setInt(1, serviceorderid);
	 csmt.executeQuery();
	 myDB.closeConnection();
	}
	
	public void ServiceOrderAssign(int serviceorderid, int userid)
	 throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException
	{
	 String mySQL = "{call ServiceOrderAssign(?,?)}";
	 myDB.createConnection();
	 csmt = myDB.conn.prepareCall(mySQL);
	 csmt.setInt(1, serviceorderid);
	 csmt.setInt(2, userid);
	 csmt.executeQuery();
	 myDB.closeConnection();
	}
	
	public void SendToDispatch(int serviceorderid, int userid)
	 throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException
	{
	 String mySQL = "{call SendToDispatch(?,?)}";
	 myDB.createConnection();
	 csmt = myDB.conn.prepareCall(mySQL);
	 csmt.setInt(1, serviceorderid);
	 csmt.setInt(2, userid);
	 csmt.executeQuery();
	 myDB.closeConnection();
	}
	
	public void ServiceOrderCheckin(int serviceorderid, int sostatusid, int userid, String timeestimate, String actionnotes)
	 throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException
	{
	 String mySQL = "{call ServiceOrderCheckin(?,?,?,?,?)}";
	 myDB.createConnection();
	 csmt = myDB.conn.prepareCall(mySQL);
	 csmt.setInt(1, serviceorderid);
	 csmt.setInt(2, sostatusid);
	 csmt.setInt(3, userid);
	 csmt.setString(4, timeestimate);
	 csmt.setString(5, actionnotes);
	 csmt.executeQuery();
	 myDB.closeConnection();
	}
	
	public void ServiceOrderCheckout(int serviceorderid, int sostatusid, int userid, String timeestimate, String actionnotes)
	 throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException
	{
	 String mySQL = "{call ServiceOrderCheckout(?,?,?,?,?)}";
	 myDB.createConnection();
	 csmt = myDB.conn.prepareCall(mySQL);
	 csmt.setInt(1, serviceorderid);
	 csmt.setInt(2, sostatusid);
	 csmt.setInt(3, userid);
	 csmt.setString(4, timeestimate);
	 csmt.setString(5, actionnotes);
	 csmt.executeQuery();
	 myDB.closeConnection();
	}
	
	public void ServiceOrderCheckoutFinal(int serviceorderid, int sostatusid, int userid, String timeestimate, String actionnotes, String resolutionnotes)
	 throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException
	{
	 String mySQL = "{call ServiceOrderCheckoutFinal(?,?,?,?,?,?)}";
	 myDB.createConnection();
	 csmt = myDB.conn.prepareCall(mySQL);
	 csmt.setInt(1, serviceorderid);
	 csmt.setInt(2, sostatusid);
	 csmt.setInt(3, userid);
	 csmt.setString(4, timeestimate);
	 csmt.setString(5, actionnotes);
	 csmt.setString(6, resolutionnotes);
	 csmt.executeQuery();
	 myDB.closeConnection();
	}
	
	public void closeConnection()
	throws ClassNotFoundException, SQLException, InstantiationException, IllegalAccessException {
		 myDB.closeConnection();
	}
	


}
